﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_StockDetail.cs
// 创建时间：2016/11/29
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  库存详情表.
    /// </summary>
    public partial class ERP_StockDetailDAL : IERP_StockDetailDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_StockDetail表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的

		private const string QueryDataString = @" select a.*,b.DetailId,b.StoreRoomId,
c.Supplyprice,c.SupplyId,
d.CargoCode,d.CargoName,d.ByName,d.Spec,d.Version,d.Spell,d.Unit,d.Area,d.ProduceName,
(select SupplyName from ERP_Supply where SupplyID=c.SupplyID )  SupplyName,
(select TYPE_NAME from ERP_Type where TYPE_ID=d.TypeId )  TypeName,
(select Use_NAME from ERP_Use where Use_ID=d.UseId )  UseNAME,
(c.Supplyprice * a.Count) TotalPrice
from ERP_StockDetail a 
inner join ERP_StockInfo b on a.StoreId = b.StoreId
inner join  ERP_Cargo_Detail c on b.DetailId=c.DetailId
inner join ERP_Cargo d on c.CargoId=d.CargoId ";


        public DataSet GetListCustomer(string sWhere, string sOder)
        {
            string basesql = @"(
select a.*,
b.StoreRoomId,
c.Supplyprice,(c.Supplyprice * a.Count) TotalPrice,c.State,
d.CargoId,d.CargoName,d.CargoCode,d.Spec,d.Version,d.Spell,d.Area,d.Unit,d.ProduceName,
f.CustomId,f.CargoName CusCargoName,f.CargoCode CusCargoCode,f.Spell CusSpell,
g.DetailId CusDetailId,g.Supplyprice CusSupplyprice,g.State CusDetailState
from ERP_StockDetail a 
inner join ERP_StockInfo b on a.StoreId = b.StoreId
inner join  ERP_Cargo_Detail c on b.DetailId=c.DetailId
inner join ERP_Cargo d on c.CargoId=d.CargoId 
inner join ERP_CgRelations e on e.CargoId =d.CargoId
inner join ERP_CusCargo f on e.CusCargoId = f.CargoId
inner join ERP_CusCargo_Detail g on g.CargoId = f.CargoId
) SQLA";

            string sqlStr = "select * from " + basesql;

            if (sWhere.Length > 0)
                sqlStr += " where " + sWhere;
            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;

            WriteLog("ERP_StockDetailDAL", "GetListCustomer(string sWhere, string sOder)", sqlStr);

            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }


        /// <summary>
        /// 获取锁定的物品数量
        /// </summary>
        /// <param name="itemId"></param>
        /// <returns></returns>
        public string GetLockCargo(CommonLib.Model.DBTransaction dbTrans, int itemId, int needCount)
        {
            string sql = string.Format(@"select Count SUMCount from ERP_StockDetail where ItemId = {0} ;
select ISNULL(SUM(Count),0) LockCount
 from (
select a.ItemId,a.Count,b.BoundNo  BussNo,'出库' BussType,b.State BussState from ERP_OutBoundDetail a
inner join ERP_OutBound b on b.BoundId = a.DBoundId 
where b.State<2 and ItemId={0}
union all
select a.ItemId,a.Count,b.ReturnNo BussNo,'退货' BussType,b.State  from ERP_RetuenCargoDetail a
inner join ERP_RetuenCargo b on b.ReturnID = a.DReturnID
where b.State<2 and ItemId={0}
union all
select a.ItemId,a.Count,b.LendNo BussNo,'借出' BussType,b.State  from ERP_Lend_Detail a
inner join ERP_Lend b on b.LendId = a.LendId
where b.State<2 and ItemId={0}
) tab1  ", itemId);
            DataSet rdt = SqlHelper.ExecuteDataset(dbTrans.sql_trans, CommandType.Text, sql);
            int SUMCount = Convert.ToInt32(rdt.Tables[0].Rows[0]["SUMCount"]);
            int LockCount = Convert.ToInt32(rdt.Tables[1].Rows[0]["LockCount"]);
            //string BatchNo = row["BatchNo"].ToString();
            //string CargoName = row["CargoName"].ToString();
            if (needCount > (SUMCount - LockCount))
            {
                string msg = string.Format("总数{0}个,占用数量{1}个", SUMCount, LockCount);
                return msg;
            }
            return null;

        }


    }
}
